#include "stydatabase.h"
#include <QDate>

QVector<struct styData> g_data;
//struct Users *user=NULL;

static struct MyDatabase myDatabase =
{
    .sql_host = "139.199.172.253",
    .sql_db_name = "stySystem",
    .sql_db_passwd = "!$@*%&Hzq076923",
    .sql_user_name = "root",
    .sql_port = 3306,
};
styDatabase::styDatabase()
{
    //1.查询支持的数据库类型，调试时候用
    //avail_drivers();

    //user = (struct Users)calloc(1,sizeof(struct Users));
    //user = new struct Users;

    //friendOpt(0,QString("dsqs"),QString("cjl"),55);
}

/**
 * @brief styDatabase::userAuthenticate
 *          验证用户名与密码,如果正确则
 * @param UName
 * @param UPassword
 * @return 
 */
bool styDatabase::userAuthenticate(QString UName, QString UPassword)
{
    QSqlQuery query;
    query.prepare("select UName from Users where UName=? and UPassword=?");
    query.bindValue(0,UName);
    query.bindValue(1,UPassword);
    query.exec();
    if(query.size()>0){
        query.next();
        return inquireAllInfo(query.value(0).toString());
    }else{
        qDebug()<<"no such user??";
        return false;
    }
}

/**
 * @brief styDatabase::createConnection
 *      连接数据库
 * @return
 */
bool styDatabase::createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");//指定数据库驱动
    db.setHostName(QString(myDatabase.sql_host));
    db.setDatabaseName(QString(myDatabase.sql_db_name));
    db.setPassword(QString(myDatabase.sql_db_passwd));
    db.setPort(myDatabase.sql_port);
    db.setUserName(QString(myDatabase.sql_user_name));

    if(!db.open()){
        QMessageBox::critical(0,"Cannot open database",
                              "未能与数据库建立连接",QMessageBox::Cancel);
        return false;
    }
    return true;
}

bool styDatabase::inquireAllInfo(QString UName)
{
    if(inquireOneInfo(UName)==0)
        return false;
    QSqlQuery query;
      QString queryString = QString("select FFriName from Friends where FName=\"%1\"").arg(UName);
    //qDebug()<<queryString;
    query.prepare(queryString);
    query.exec();
    //qDebug()<<"fri query.size():"<<query.size();
    while(query.next()){
        if(inquireOneInfo(query.value(0).toString())==false)
            return false;
    }
    return true;
}

bool styDatabase::inquireOneInfo(QString UName)
{
    struct styData userData;
    QSqlQuery query;
    /* 1.先查询个人信息 */
    QString queryString = QString("select UName,UNickname,UPassword,USex,UBirthday,UAddress,UPhone,UEmail,ULearnGoals,ULearnDcla,UTgDayTime,UTgDay,UDeadline,UPicture from Users where UName=\"%1\"").arg(UName);
    //qDebug()<<queryString;
    query.prepare(queryString);
    query.exec();
    //qDebug()<<"user info query.size():"<<query.size();
    while(query.next()){
        userData.friends.UName = query.value(0).toString();
        userData.friends.UNickname = query.value(1).toString();
        userData.friends.UPassword = query.value(2).toString();
        userData.friends.USex = query.value(3).toString();
        userData.friends.UBirthday = query.value(4).toDate();
        userData.friends.UAddress = query.value(5).toString();
        userData.friends.UPhone = query.value(6).toString();
        userData.friends.UEmail = query.value(7).toString();
        userData.friends.ULearnGoals = query.value(8).toString();
        userData.friends.ULearnDcla = query.value(9).toString();
        userData.friends.UTgDayTime = query.value(10).toFloat();
        userData.friends.UTgDay = query.value(11).toInt();
        userData.friends.UDeadline = query.value(12).toDate();
        userData.friends.UPicture.loadFromData(query.value(13).toByteArray(),"JPG");
    }
    /* 2.查询拥有的笔(每个用户最多拥有一支) */
    queryString = QString("select PIdent from Pens where POwner = \"%1\"").arg(UName);
    //qDebug()<<queryString;
    query.prepare(queryString);
    query.exec();
    //qDebug()<<"user info query.size():"<<query.size();
    while(query.next()){
        userData.friends.penName=query.value(0).toString();
    }
    /* 3.查询本月的学习数据 */
    //......放到main_left_widget.cpp里面去查了
    g_data<<userData;
    return true;
}

/**
 * @brief styDatabase::friendOpt
 * @param opt   操作码，0：删除，1：添加 2：修改亲密度
 * @param FName 用户名
 * @param FFriName  朋友的用户名
 * @param FRel  relation,关系，即亲密程度
 * @return      成功返回true,失败返回false
 * @example
 *      friendOpt(0,QString("dsqs"),QString("cjl"),55);
 */
bool styDatabase::friendOpt(int opt,QString FName, QString FFriName, int FRel=50)
{
    QSqlQuery fquery;
    switch(opt){
    case 0:
        fquery.prepare("delete from Friends where FName = ? and FFriName = ?");
        break;
    case 1:
        fquery.prepare("insert into Friends (FName,FFriName,FRel) values(?,?,?)");
        break;
    default:
        fquery.prepare("updata Friends set FRel=? where FName = ? and FFriName = ?");
    }
    if(opt==2)
        fquery.addBindValue(FRel);
    fquery.addBindValue(FName);
    fquery.addBindValue(FFriName);
    if(opt==1)
        fquery.addBindValue(FRel);

    if(!fquery.exec()){
        QSqlError error = fquery.lastError();
        qDebug()<<"table Friends 操作失败："<<error<<endl;
        return false;
    }
    else{
        qDebug()<<"插入成功\n";
        return true;
    }
}
/**
 * @brief styDatabase::avail_drivers
 *  打印支持的数据库类型，调试时候用到
 */
void styDatabase::avail_drivers()
{
    qDebug()<<"Available drivers:";
    QStringList drivers=QSqlDatabase::drivers();
    QString dirver;
    foreach(dirver,drivers)
        qDebug()<<dirver;
}


/**
 * @brief releaseDynamic 向数据表DailySummary添加一条新记录
 * @param content   日志的内容
 * @param file_name 图片文件名
 */
void releaseDynamic(QString content, QString file_name)
{
    QByteArray img;
    QFile f(file_name);
    if(f.open(QIODevice::ReadOnly)){
        img=f.readAll();
        f.close();
    }
    QSqlQuery query;
    query.prepare("INSERT INTO DailySummary "
                  "(DName,DDate,DTime,DContent,DPraise,DPicture) "
                  "VALUES (?,?,?,?,?,?)");
    query.bindValue(0,g_data[0].friends.UName);
    query.bindValue(1,QDate::currentDate().toString("yyyy-MM-dd"));
    query.bindValue(2,QTime::currentTime().toString("hh:mm:ss"));
    query.bindValue(3,content);
    query.bindValue(4,"");
    query.bindValue(5,img);
    query.exec();
    if(query.lastError().isValid()){
        qDebug() << query.lastError().text();
        QSqlDatabase::database().rollback();//回滚数据库上的事务??
    }else{
        qDebug()<<"insert success!";
    }
}
/**
 * @brief getAllDailySummary    获取所有的每日总结信息
 * @param allDailySummary
 */
void getAllDailySummary(QVector<DailySummary> &allDailySummary)
{
    allDailySummary.clear();
    QSqlQuery query;
    for(int i=0;i<g_data.size();i++){
        query.prepare("select DName,DDate,DTime,DContent,DPraise,DPicture "
                      "from DailySummary where DName=?");
        query.bindValue(0,g_data[i].friends.UName);
        query.exec();
        if(query.lastError().isValid()){
            qDebug() <<"line"<<__LINE__<<":"<< query.lastError().text();
            QSqlDatabase::database().rollback();//回滚数据库上的事务??
        }
        if(query.next()){
            struct DailySummary oneSummary;
            oneSummary.DName=g_data[i].friends.UNickname;
            oneSummary.DDate=query.value(1).toDate();
            oneSummary.DTime=query.value(2).toTime();
            oneSummary.DContent=query.value(3).toString();
            oneSummary.DPraise=query.value(4).toString();
            oneSummary.DPicture.loadFromData(query.value(5).toByteArray(),"JPG");
            allDailySummary<<oneSummary;
        }else{
            qDebug()<<g_data[i].friends.UNickname<<" has no daily Summary released!";
        }
    }
}
